﻿using Sys.Data;
using Sys.Model.BpamsModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Sys.Core.House
{
    /// <summary>
    /// BPAMS系统_业务逻辑
    /// Luo.Z.X 20150827
    /// </summary>
    public class BpamsBll
    {
        #region 单例
        private static readonly BpamsBll instance = new BpamsBll();

        public static BpamsBll Instance
        {
            get
            {
                return instance;
            }
        }
        #endregion

        #region 区域
        /// <summary>
        /// 读取区域信息的SQL语句
        /// </summary>
        private readonly string SQL_GetRegions = @"select * from CD_Region order by Sequence";

        /// <summary>
        /// 获取区域信息（根据ID）语句
        /// </summary>
        private readonly string SQL_GetRegion = @"SELECT * FROM CD_Region WHERE ID='{0}'";

        /// <summary>
        /// 获取区域信息列表
        /// </summary>
        /// <returns></returns>
        public List<Bpams_Region> GetRegions()
        {
            List<Bpams_Region> result = new List<Bpams_Region>();
            var sdr = SqlHelper.GetSqlDataReader(SQL_GetRegions);
            if (sdr != null)
            {
                Bpams_Region region = null;
                while (sdr.Read())
                {
                    region = new Bpams_Region();
                    Guid guid = Guid.Empty;
                    Guid.TryParse(sdr["ID"].ToString(), out guid);
                    region.ID = guid;
                    region.Description = sdr["Description"].ToString();
                    int sequence = 0;
                    int.TryParse(sdr["Sequence"].ToString(), out sequence);
                    region.Sequence = sequence;
                    result.Add(region);
                }
            }
            return result;
        }

        /// <summary>
        /// 获取区域信息（根据ID）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Bpams_Region GetRegion(Guid id)
        {
            Bpams_Region result = new Bpams_Region();
            string sqlString = string.Format(SQL_GetRegion, id);
            var sdr = SqlHelper.GetSqlDataReader(sqlString);
            if (sdr != null)
            {
                sdr.Read();

                result.ID = id;

                result.Description = sdr["Description"].ToString();

                int sequence = 0;
                int.TryParse(sdr["Sequence"].ToString(), out sequence);
                result.Sequence = sequence;
            }
            return result;
        }
        #endregion

        #region 房源分类
        /// <summary>
        /// 读取房源分类信息的SQL语句
        /// </summary>
        private readonly string SQL_GetHouseCategories = @"select * from CD_HouseCategory order by Sequence";

        /// <summary>
        /// 获取房源分类信息（根据ID）SQL语句
        /// </summary>
        private readonly string SQL_GetHouseCategory = @"select * from CD_HouseCategory WHERE ID='{0}'";

        /// <summary>
        /// 获取房源分类信息列表
        /// </summary>
        /// <returns></returns>
        public List<Bpams_HouseCategory> GetHouseCategorys()
        {
            List<Bpams_HouseCategory> result = new List<Bpams_HouseCategory>();
            var sdr = SqlHelper.GetSqlDataReader(SQL_GetHouseCategory);
            if (sdr != null)
            {
                Bpams_HouseCategory category = null;
                while (sdr.Read())
                {
                    category = new Bpams_HouseCategory();
                    Guid guid = Guid.Empty;
                    Guid.TryParse(sdr["ID"].ToString(), out guid);
                    category.ID = guid;
                    category.Description = sdr["Description"].ToString();
                    int sequence = 0;
                    int.TryParse(sdr["Sequence"].ToString(), out sequence);
                    category.Sequence = sequence;
                    result.Add(category);
                }
            }
            return result;
        }

        /// <summary>
        /// 获取房源分类信息（根据ID）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Bpams_HouseCategory GetHouseCategory(Guid id)
        {
            Bpams_HouseCategory result = new Bpams_HouseCategory();
            string sqlString = string.Format(SQL_GetHouseCategory, id);
            var sdr = SqlHelper.GetSqlDataReader(sqlString);
            if (sdr != null)
            {
                sdr.Read();

                result.ID = id;

                result.Description = sdr["Description"].ToString();

                int sequence = 0;
                int.TryParse(sdr["Sequence"].ToString(), out sequence);
                result.Sequence = sequence;
            }
            return result;
        }
        #endregion

        #region 户型分类
        /// <summary>
        /// 读取户型分类信息的SQL语句
        /// </summary>
        private readonly string SQL_GetGapCategories = @"select * from CD_GapCategory order by Sequence";

        /// <summary>
        /// 获取户型分类信息（根据ID）SQL语句
        /// </summary>
        private readonly string SQL_GetGapCategory = @"select * from CD_GapCategory WHERE ID='{0}'";

        /// <summary>
        /// 获取户型分类信息列表
        /// </summary>
        /// <returns></returns>
        public List<Bpams_GapCategory> GetGapCategorys()
        {
            List<Bpams_GapCategory> result = new List<Bpams_GapCategory>();
            var sdr = SqlHelper.GetSqlDataReader(SQL_GetGapCategories);
            if (sdr != null)
            {
                Bpams_GapCategory category = null;
                while (sdr.Read())
                {
                    category = new Bpams_GapCategory();
                    Guid guid = Guid.Empty;
                    Guid.TryParse(sdr["ID"].ToString(), out guid);
                    category.ID = guid;
                    category.Description = sdr["Description"].ToString();
                    int sequence = 0;
                    int.TryParse(sdr["Sequence"].ToString(), out sequence);
                    category.Sequence = sequence;
                    result.Add(category);
                }
            }
            return result;
        }

        /// <summary>
        /// 获取户型分类信息（根据ID）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Bpams_GapCategory GetGapCategory(Guid id)
        {
            Bpams_GapCategory result = new Bpams_GapCategory();
            string sqlString = string.Format(SQL_GetGapCategory, id);
            var sdr = SqlHelper.GetSqlDataReader(sqlString);
            if (sdr != null)
            {
                sdr.Read();

                result.ID = id;

                result.Description = sdr["Description"].ToString();

                int sequence = 0;
                int.TryParse(sdr["Sequence"].ToString(), out sequence);
                result.Sequence = sequence;
            }
            return result;
        }
        #endregion

        #region 房源（售房、租房）
        //        /// <summary>
        //        /// 读取房源数量的SQL语句
        //        /// </summary>
        //        private readonly string SQL_GetHousesCount = @"SELECT COUNT(1) FROM House
        //                                                       WHERE FlagSell='1' AND FlagBingSell='0'
        //                                                       AND (ISNULL(BuildingNameCH,'')<>'' OR ISNULL(BuildingNameEng,'')<>'')";

        //        /// <summary>
        //        /// 读取房源信息的SQL语句（分页SQL，提高效率）
        //        /// </summary>
        //        private readonly string SQL_GetHouses = @"SELECT * FROM House
        //                                                 WHERE FlagSell='1' AND FlagBingSell='0'
        //                                                 AND (ISNULL(BuildingNameCH,'')<>'' OR ISNULL(BuildingNameEng,'')<>'')
        //                                                 ORDER BY LastUpdTime DESC,CreateTime DESC,JoinDate DESC
        //                                                 OFFSET {0} ROWS
        //                                                 FETCH NEXT {1} ROWS ONLY";
        /// <summary>
        /// 根据id获取房源信息
        /// </summary>
        private readonly string SQL_GetHouse = @"select h.*,  --房源信息
s.ChineseName UpdateUser,s.Tel UpdateUserTel,  --更新人，更新人电话
r.[Description] Region,  --区域名称
c.[Description] HouseCategory  --房源类型名称
from House h
inner join sys_Staff s on h.StaffNo_Update=s.StaffNo
inner join CD_Region r on h.RegionID=r.ID
inner join CD_HouseCategory c on h.HouseCategoryID=c.ID
WHERE h.ID='{0}'";

        /// <summary>
        /// 售房信息个数
        /// </summary>
        private readonly string SQL_GetSellHouseCount = @"select Count(1)
from House h
inner join sys_Staff s on h.StaffNo_Update=s.StaffNo
inner join CD_Region r on h.RegionID=r.ID
inner join CD_HouseCategory c on h.HouseCategoryID=c.ID
where h.FlagBingSell='1' and h.FlagSell='0' and h.FlagRent='0'  --出售中";

        /// <summary>
        /// 售房信息
        /// </summary>
        private readonly string SQL_GetSellHouses = @"select h.*,  --房源信息
s.ChineseName UpdateUser,s.Tel UpdateUserTel,  --更新人，更新人电话
r.[Description] Region,  --区域名称
c.[Description] HouseCategory  --房源类型名称
from House h
inner join sys_Staff s on h.StaffNo_Update=s.StaffNo
inner join CD_Region r on h.RegionID=r.ID
inner join CD_HouseCategory c on h.HouseCategoryID=c.ID
where h.FlagBingSell='1' and h.FlagSell='0' and h.FlagRent='0' order by h.LastUpdTime desc,h.CreateTime desc  --出售中，按更新时间降序，再按创建时间降序
OFFSET {0} ROWS
FETCH NEXT {1} ROWS ONLY";

        /// <summary>
        /// 租房信息个数
        /// </summary>
        private readonly string SQL_GetRentHouseCount = @"select Count(1)
from House h
inner join sys_Staff s on h.StaffNo_Update=s.StaffNo
inner join CD_Region r on h.RegionID=r.ID
inner join CD_HouseCategory c on h.HouseCategoryID=c.ID
where FlagBingRent='1' and FlagRent='0' and FlagSell='0'  --出租中";

        /// <summary>
        /// 租房信息
        /// </summary>
        private readonly string SQL_GetRentHouses = @"select h.*,  --房源信息
s.ChineseName UpdateUser,s.Tel UpdateUserTel,  --更新人，更新人电话
r.[Description] Region,  --区域名称
c.[Description] HouseCategory  --房源类型名称
from House h
inner join sys_Staff s on h.StaffNo_Update=s.StaffNo
inner join CD_Region r on h.RegionID=r.ID
inner join CD_HouseCategory c on h.HouseCategoryID=c.ID
where FlagBingRent='1' and FlagRent='0' and FlagSell='0' order by h.LastUpdTime desc,h.CreateTime desc  --出租中，按更新时间降序，再按创建时间降序
OFFSET {0} ROWS
FETCH NEXT {1} ROWS ONLY";

        /// <summary>
        /// 获取房源的数量
        /// </summary>
        /// <returns></returns>
        public int GetHousesCount(HouseGetType houseGetType)
        {
            int result = 0;
            string sqlString = string.Empty;
            switch (houseGetType)
            {
                case HouseGetType.Sell:
                    sqlString = SQL_GetSellHouseCount;
                    break;
                case HouseGetType.Rent:
                    sqlString = SQL_GetRentHouseCount;
                    break;
            }
            var exeResult = SqlHelper.GetSqlScalar(sqlString);
            int.TryParse(exeResult.ToString(), out result);
            return result;
        }

        /// <summary>
        /// 获取房源信息列表（直接分页SQL）
        /// </summary>
        /// <param name="skip">跳过多少条记录</param>
        /// <param name="take">取多少条记录</param>
        /// <returns></returns>
        public List<Bpams_House> GetHouses(HouseGetType houseGetType, int skip, int take)
        {
            List<Bpams_House> result = new List<Bpams_House>();
            string sqlString = string.Empty;
            switch (houseGetType)
            {
                case HouseGetType.Sell:
                    sqlString = SQL_GetSellHouses;
                    break;
                case HouseGetType.Rent:
                    sqlString = SQL_GetRentHouses;
                    break;
            }
            sqlString = string.Format(sqlString, skip, take);
            var sdr = SqlHelper.GetSqlDataReader(sqlString);
            if (sdr != null)
            {
                Bpams_House house = null;
                while (sdr.Read())
                {
                    house = new Bpams_House();

                    Guid guid = Guid.Empty;
                    Guid.TryParse(sdr["ID"].ToString(), out guid);
                    house.ID = guid;

                    house.SN = sdr["SN"].ToString();

                    guid = Guid.Empty;
                    Guid.TryParse(sdr["RegionID"].ToString(), out guid);
                    house.RegionID = guid;
                    house.Region = sdr["Region"].ToString();

                    guid = Guid.Empty;
                    Guid.TryParse(sdr["HouseCategoryID"].ToString(), out guid);
                    house.HouseCategoryID = guid;
                    house.Region = sdr["HouseCategory"].ToString();

                    house.JoinDate = sdr["JoinDate"].ToString();
                    house.BuildingNameCH = sdr["BuildingNameCH"].ToString();
                    house.BuildingNameEng = sdr["BuildingNameEng"].ToString();
                    house.StreetNameCH = sdr["StreetNameCH"].ToString();
                    house.StreetNameEng = sdr["StreetNameEng"].ToString();
                    house.StreetNum = sdr["StreetNum"].ToString();
                    house.HouseAge = sdr["HouseAge"].ToString();
                    house.SitNumber = sdr["SitNumber"].ToString();
                    house.FloorNumber = sdr["FloorNumber"].ToString();
                    house.UnitNumber = sdr["UnitNumber"].ToString();

                    guid = Guid.Empty;
                    Guid.TryParse(sdr["GapCategoryID"].ToString(), out guid);
                    house.GapCategoryID = guid;

                    house.FeetNumber = sdr["FeetNumber"].ToString();
                    house.PracticalArea = sdr["PracticalArea"].ToString();
                    house.WindowsArea = sdr["WindowsArea"].ToString();
                    house.GarageNumber = sdr["GarageNumber"].ToString();
                    house.PlatformNumber = sdr["PlatformNumber"].ToString();

                    //租價單位為元，不保留小數
                    decimal rentPrice = 0;
                    decimal.TryParse(sdr["RentPrice"].ToString(), out rentPrice);
                    rentPrice = decimal.Round(rentPrice, 0);
                    house.RentPrice = rentPrice.ToString();
                    //售价单位为萬
                    decimal sellPrice = 0;
                    decimal.TryParse(sdr["SellPrice"].ToString(), out sellPrice);
                    sellPrice = decimal.Round(sellPrice / 10000, 0);
                    house.SellPrice = string.Format("{0}萬", sellPrice);

                    house.AvgFeetPrice = sdr["AvgFeetPrice"].ToString();
                    house.Landscape = sdr["Landscape"].ToString();
                    house.Remark = sdr["Remark"].ToString();

                    bool flag = false;
                    Boolean.TryParse(sdr["FlagRent"].ToString(), out flag);
                    house.FlagRent = flag;

                    flag = false;
                    Boolean.TryParse(sdr["FlagSell"].ToString(), out flag);
                    house.FlagSell = flag;

                    flag = false;
                    Boolean.TryParse(sdr["FlagBingRent"].ToString(), out flag);
                    house.FlagBingRent = flag;

                    flag = false;
                    Boolean.TryParse(sdr["FlagBingSell"].ToString(), out flag);
                    house.FlagBingSell = flag;

                    house.RentEndDate = sdr["RentEndDate"].ToString();
                    house.StaffNo_Create = sdr["StaffNo_Create"].ToString();
                    house.StaffNo_Update = sdr["StaffNo_Update"].ToString();
                    house.UpdateUser = sdr["UpdateUser"].ToString();
                    house.UpdateUserTel = sdr["UpdateUserTel"].ToString();
                    house.CreateTime = sdr["CreateTime"].ToString();
                    house.LastUpdTime = sdr["LastUpdTime"].ToString();
                    house.ZXLPage = sdr["ZXLPage"].ToString();
                    house.TZPage = sdr["TZPage"].ToString();
                    house.OrgCode = sdr["OrgCode"].ToString();

                    result.Add(house);
                }
            }
            return result;
        }

        /// <summary>
        /// 获取房源信息（根据ID）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Bpams_House GetHouse(string id)
        {
            Bpams_House house = new Bpams_House();
            string sqlString = string.Format(SQL_GetHouse, id);
            var sdr = SqlHelper.GetSqlDataReader(sqlString);
            if (sdr != null)
            {
                sdr.Read();

                Guid guid = Guid.Empty;
                Guid.TryParse(id, out guid);
                house.ID = guid;

                house.SN = sdr["SN"].ToString();

                guid = Guid.Empty;
                Guid.TryParse(sdr["RegionID"].ToString(), out guid);
                house.RegionID = guid;

                house.Region = sdr["Region"].ToString();
                //if (house.RegionID.HasValue && house.RegionID != Guid.Empty)
                //    house.Region = GetRegion(house.RegionID.Value).Description;

                guid = Guid.Empty;
                Guid.TryParse(sdr["HouseCategoryID"].ToString(), out guid);
                house.HouseCategoryID = guid;

                house.HouseCategory = sdr["HouseCategory"].ToString();
                //if (house.HouseCategoryID.HasValue && house.HouseCategoryID != Guid.Empty)
                //    house.HouseCategory = GetHouseCategory(house.HouseCategoryID.Value).Description;

                house.JoinDate = sdr["JoinDate"].ToString();
                house.BuildingNameCH = sdr["BuildingNameCH"].ToString();
                house.BuildingNameEng = sdr["BuildingNameEng"].ToString();
                house.StreetNameCH = sdr["StreetNameCH"].ToString();
                house.StreetNameEng = sdr["StreetNameEng"].ToString();
                house.StreetNum = sdr["StreetNum"].ToString();
                house.HouseAge = sdr["HouseAge"].ToString();
                house.SitNumber = sdr["SitNumber"].ToString();
                house.FloorNumber = sdr["FloorNumber"].ToString();
                house.UnitNumber = sdr["UnitNumber"].ToString();

                guid = Guid.Empty;
                Guid.TryParse(sdr["GapCategoryID"].ToString(), out guid);
                house.GapCategoryID = guid;

                if (house.GapCategoryID.HasValue && house.GapCategoryID != Guid.Empty)
                    house.GapCategory = GetGapCategory(house.GapCategoryID.Value).Description;

                house.FeetNumber = sdr["FeetNumber"].ToString();
                house.PracticalArea = sdr["PracticalArea"].ToString();
                house.WindowsArea = sdr["WindowsArea"].ToString();
                house.GarageNumber = sdr["GarageNumber"].ToString();
                house.PlatformNumber = sdr["PlatformNumber"].ToString();

                //租價單位為元，不保留小數
                decimal rentPrice = 0;
                decimal.TryParse(sdr["RentPrice"].ToString(), out rentPrice);
                rentPrice = decimal.Round(rentPrice, 0);
                house.RentPrice = rentPrice.ToString();
                //售价单位为萬
                decimal sellPrice = 0;
                decimal.TryParse(sdr["SellPrice"].ToString(), out sellPrice);
                sellPrice = decimal.Round(sellPrice / 10000, 0);
                house.SellPrice = string.Format("{0}萬", sellPrice);

                house.AvgFeetPrice = sdr["AvgFeetPrice"].ToString();
                house.Landscape = sdr["Landscape"].ToString();
                house.Remark = sdr["Remark"].ToString();

                bool flag = false;
                Boolean.TryParse(sdr["FlagRent"].ToString(), out flag);
                house.FlagRent = flag;

                flag = false;
                Boolean.TryParse(sdr["FlagSell"].ToString(), out flag);
                house.FlagSell = flag;

                flag = false;
                Boolean.TryParse(sdr["FlagBingRent"].ToString(), out flag);
                house.FlagBingRent = flag;

                flag = false;
                Boolean.TryParse(sdr["FlagBingSell"].ToString(), out flag);
                house.FlagBingSell = flag;

                house.RentEndDate = sdr["RentEndDate"].ToString();
                house.StaffNo_Create = sdr["StaffNo_Create"].ToString();
                house.StaffNo_Update = sdr["StaffNo_Update"].ToString();
                house.UpdateUser = sdr["UpdateUser"].ToString();
                house.UpdateUserTel = sdr["UpdateUserTel"].ToString();
                house.CreateTime = sdr["CreateTime"].ToString();
                house.LastUpdTime = sdr["LastUpdTime"].ToString();
                house.ZXLPage = sdr["ZXLPage"].ToString();
                house.TZPage = sdr["TZPage"].ToString();
                house.OrgCode = sdr["OrgCode"].ToString();
            }
            return house;
        }
        #endregion
    }

    public enum HouseGetType
    {
        /// <summary>
        /// 售房
        /// </summary>
        Sell,
        /// <summary>
        /// 租房
        /// </summary>
        Rent
    }
}
